package com.ukefu.webim.service.repository;

import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.ukefu.webim.web.model.AgentUser;

public abstract interface AgentUserRepository  extends JpaRepository<AgentUser, String>
{
	public abstract AgentUser findByIdAndOrgi(String paramString, String orgi);

	public abstract List<AgentUser> findByUseridAndOrgi(String userid, String orgi);

	public abstract List<AgentUser> findByAgentnoAndOrgi(String agentno , String orgi , Sort sort);

	public abstract Page<AgentUser> findByOrgiAndStatus(String orgi ,String status , Pageable page);
	
	public abstract List<AgentUser> findByAgentnoAndStatusAndOrgi(String agentno ,String status , String orgi);
	
	public abstract int countByStatusAndOrgiAndOrdertimeLessThan(String status,String orgi , Date ordertime);
	
	public abstract int countByStatusAndOrgi(String status,String orgi);
	
	public abstract int countByStatusAndSkillAndOrgi(String status,String skill,String orgi);
	
	public abstract int countByStatusAndAgentAndOrgi(String status,String agent,String orgi);
	
	public abstract int countByAgentnoAndStatusAndOrgi(String agentno ,String status , String orgi);

	public abstract AgentUser findOneByAgentnoAndStatusAndOrgi(String id, String status, String orgi);
	
	public Page<AgentUser> findAll(Specification<AgentUser> spec, Pageable pageable);  //分页按条件查询


	@Query(value = "SELECT s.* FROM uk_agentuser s LEFT JOIN uk_tenant a ON s.orgi = a.tenantcode WHERE 1=1 " +
			"AND IF (?1!='',s.creater = ?1,1=1) AND IF (?2!='',a.tenantname like ?2,1=1) AND IF (?3!='',a.tenantcode = ?3,1=1)  \n#pageable\n"
			, countQuery="SELECT count(s.id) FROM uk_agentuser s LEFT JOIN uk_tenant a ON s.orgi = a.tenantcode WHERE 1=1 " +
			"AND IF (?1!='',s.creater = ?1,1=1) AND IF (?2!='',a.tenantname like ?2,1=1) AND IF (?3!='',a.tenantcode = ?3,1=1) "
			,nativeQuery = true)
	public abstract Page<AgentUser> findByManyTerm(String creater, String shopname, String shoporgi, Pageable pageable) ;

	@Query("select count(case when usernum is null then usernum else 0 end) from AgentUser where creater = ?1 ")
	Long sumUsernumByCreater(String userid);

	@Query("select count(case when usernum is null then usernum else 0 end) from AgentUser where creater = ?1 and userid = ?2 ")
	Long sumUsernumByCreaterAndUserid(String userid,String uid);

	public abstract List<AgentUser> findByCreaterAndUserid(String creater , String userid);

	public abstract List<AgentUser> findByCreater(String creater);

    public abstract List<AgentUser> findAll(Specification<AgentUser> spec , Sort sort) ;
}
